﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SISTEMA_ALPROVIDE.Model;
using SISTEMA_ALPROVIDE.Bean;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace SISTEMA_ALPROVIDE.DAO
{
    public class DAOVenta
    {
        private ConexionBD conn = new ConexionBD();

        public int RegistrarVenta(Venta venta)
        {
            int valor = 0;
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                comm.CommandText = "INSERT venta (FECHA,TOTAL,IDEMPLEADO,IDTIENDA,IDCLIENTE) VALUES ('" + venta.Fecha.ToString("yyyy-MM-dd") + "'," +venta.Total + "," + venta.IdEmpleado + "," + venta.IdTienda + "," + venta.IdCliente + ")";

                comm.ExecuteNonQuery();

                MySqlCommand comm1 = new MySqlCommand();
                comm1.Connection = conn.getConexion;
                comm1.CommandText = "SELECT MAX(IDVENTA) FROM venta";

                valor = Convert.ToInt32(comm1.ExecuteScalar());

                return valor;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                throw e;
            }
            finally {
                conn.Close();
            }
        }

        public void RegistrarLineaVenta(List<LineaVenta> listaLineaVenta, int idVenta)
        {
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                for (int i=0;i<listaLineaVenta.Count;i++){
                    comm.CommandText = "INSERT linea_venta (IDPRODUCTO,SUBTOTAL,CANTPRODUCTO,IDVENTA) VALUES (" + listaLineaVenta[i].IdProducto + "," + listaLineaVenta[i].Subtotal + "," + listaLineaVenta[i].CantidadProducto + "," + idVenta + ")";
                    comm.ExecuteNonQuery();
                }

                for (int i = 0; i < listaLineaVenta.Count; i++) {
                    comm.CommandText = "UPDATE producto SET STOCK = STOCK - " + listaLineaVenta[i].CantidadProducto + " WHERE IDPRODUCTO = " + listaLineaVenta[i].IdProducto + "";
                    comm.ExecuteNonQuery();
                }
                
            }
            catch (Exception e)
            {
                throw e;
            }
            finally {
                conn.Close();
            }
        }

        public List<Venta> ObtenerVenta(int idempleado, int idventa, DateTime fechaInicio, DateTime fechaFin)
        {
            List<Venta> listaVenta = new List<Venta>();
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                comm.CommandText = "SELECT * FROM venta where ESTADO = 1 AND FECHA BETWEEN '" + fechaInicio.ToString("yyyy-MM-dd") + "' AND '" + fechaFin.ToString("yyyy-MM-dd") + "'";

                if (idempleado != 0)
                {
                    comm.CommandText += " AND IDEMPLEADO = " + idempleado + "";
                }
                if (idventa != 0)
                {
                    comm.CommandText += " AND IDVENTA =" + idventa + "";
                }

                MySqlDataReader myReader = comm.ExecuteReader();

                while (myReader.Read())
                {
                    Venta venta = new Venta();
                    venta.IdCliente = Convert.ToInt32(myReader["IDCLIENTE"]);
                    venta.IdEmpleado = Convert.ToInt32(myReader["IDEMPLEADO"]);
                    venta.IdVenta = Convert.ToInt32(myReader["IDVENTA"]);
                    venta.Fecha = Convert.ToDateTime(myReader["FECHA"]);
                    venta.Total = Convert.ToDouble(myReader["TOTAL"]);
                    venta.IdTienda = Convert.ToInt32(myReader["IDTIENDA"]);

                    listaVenta.Add(venta);

                }
                return listaVenta;

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<LineaVenta> obtenerLineaVenta(int idVenta)
        {
            List<LineaVenta> listaLineaVenta = new List<LineaVenta>();
            try
            {
                conn.Open();

                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                comm.CommandText = "SELECT * FROM linea_venta where ESTADO = 1 AND IDVENTA = " + idVenta + "";
                
                MySqlDataReader myReader = comm.ExecuteReader();

                while (myReader.Read())
                {
                    LineaVenta lineaVenta = new LineaVenta();

                    lineaVenta.IdLineaVenta = Convert.ToInt32(myReader["IDLINEAVENTA"]);
                    lineaVenta.IdProducto = Convert.ToInt32(myReader["IDPRODUCTO"]);
                    lineaVenta.Subtotal = Convert.ToDouble(myReader["SUBTOTAL"]);
                    lineaVenta.CantidadProducto = Convert.ToInt32(myReader["CANTPRODUCTO"]);
                    lineaVenta.IdVenta = Convert.ToInt32(myReader["IDVENTA"]);

                    listaLineaVenta.Add(lineaVenta);

                }
                return listaLineaVenta;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally {
                conn.Close();
            }
        }

        public void EliminarVenta(int idVenta)
        {
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                comm.CommandText = "UPDATE venta SET ESTADO = 0 WHERE IDVENTA = " + idVenta + "";

                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally {
                conn.Close();
            }
        }

        public void EliminarLineaVenta(int idVenta,List<LineaVenta> listaLineaVenta)
        {
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                comm.CommandText = "UPDATE linea_venta SET ESTADO = 0 WHERE IDVENTA = " + idVenta + "";
                comm.ExecuteNonQuery();

                for (int i = 0; i < listaLineaVenta.Count; i++)
                {
                    comm.CommandText = "UPDATE producto SET STOCK = STOCK + " + listaLineaVenta[i].CantidadProducto + " WHERE IDPRODUCTO = " + listaLineaVenta[i].IdProducto + "";
                    comm.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }


        public void ModificarVenta(int idVenta, double total)
        {
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                comm.CommandText = "UPDATE venta SET TOTAL = " + total + " WHERE IDVENTA = " + idVenta + "";

                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public void ModificarLineaVenta(int idVenta, List<LineaVenta> listaLineaVenta)
        {
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                for (int i = 0; i < listaLineaVenta.Count; i++) {
                    comm.CommandText = "UPDATE linea_venta SET SUBTOTAL = " + listaLineaVenta[i].Subtotal +", CANTPRODUCTO = " + listaLineaVenta[i].CantidadProducto + " WHERE IDLINEAVENTA = " + listaLineaVenta[i].IdLineaVenta + "";
                    comm.ExecuteNonQuery();
                }    
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public void ModificarStock(List<int> Diferencias, List<LineaVenta> listaLineaVenta)
        {
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand();
                comm.Connection = conn.getConexion;
                for (int i = 0; i < listaLineaVenta.Count; i++)
                {
                    if (Diferencias[i] == 0)
                    {
                        continue;
                    }
                    else {
                        if (Diferencias[i] > 0)
                        {
                            comm.CommandText = "UPDATE producto SET STOCK = STOCK - " + Diferencias[i] + " WHERE IDPRODUCTO = " + listaLineaVenta[i].IdProducto + "";
                        }
                        else {
                            comm.CommandText = "UPDATE producto SET STOCK = STOCK + " + Diferencias[i]*-1 + " WHERE IDPRODUCTO = " + listaLineaVenta[i].IdProducto + "";
                        }
                    }
                    
                    comm.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}